#!/usr/bin/env python
# encoding:utf-8
# Author: jixingxing
#  ORM 之  操作表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

"""
Base.metadata.create_all(engine)  #最后执行,将所有的Base的子类,根据子类,将所有的表创建出来
"""

# 1、首先创建引擎
engine = create_engine("mysql+pymysql://testuser:testuserpasswd@112.74.209.145:3306/test1", max_overflow=5)

# engine.execute('create table tb3 (`id` int(4),`name` CHAR(40)) engine=MyISAM  default charset=utf8;')

# 2、获取一个Base对象,必须要这么做
Base = declarative_base()


# 3、创建单标
class Users(Base):  # 必须继承Base
    __tablename__ = 'users'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)  # 创建id列,整数形式,是主键
    name = Column(String(32))  # 创建name列,最大32个字节
    extra = Column(String(16))

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra')
    )


# 4、一对多
class Favor(Base):
    __tablename__ = 'favor'

    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))


# 5、多对多

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)


class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True, autoincrement=True)  # 自动增量
    hostname = Column(String(64), unique=True, nullable=False)


class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    grooup_id = Column(Integer, ForeignKey('group.id'))


# 6、写到数据库中
def init_db():
    Base.metadata.create_all(engine)


# 7、从数据库中删除
def drop_db():
    Base.metadata.drop_all(engine)


# init_db()
"""
表操作 之 增删改查
"""

Session = sessionmaker(bind=engine)
session = Session()

# 增加数据
# obj = Users(name='alex',extra='sss')  #先创建表对象,传入数据(即要添加的数据)
# session.add(obj)  # 增加数据到内存
# session.commit()  # 内存数据写入到数据

# 一次性增加多个数据  ,括号中是个列表
# session.add_all([
#     Users(name='zhoujie', extra='mather'),
#     Users(name='jiyanning', extra='son'),
#     Users(name='jixingxing', extra='father'), ]
# )
# session.commit()

# 删除数据
# session.query(Users).filter(Users.id>3).delete()   #在User表中过滤 id > 3 的使用delete() 删除
# # session.query(Users).filter(Users.id>3,Users.name='alex').delete()   # 且(and)
# session.commit()

# 修改数据 update
# session.query(Users).filter(Users.id == 3).update({'name': 'jiyanning'})
# session.query(Users).filter(Users.id >= 1).update({Users.name: Users.name + '099'},
#                                                   synchronize_session=False)  # synchronize_session=False 内部当str处理
# # session.query(Users).filter(Users.id>=1).update({'num':Users.num +1},synchronize_session=True)
# #处理数字的时候  synchronize_session 必须为True
# session.commit()

# 查询数据
ret1 = session.query(Users).all()  # 查询Users表中所有数据 ,ret1是列表,里面是对象 ,这行是Users对象
# 查看对应的sql语句
print(session.query(Users))
print(ret1)
print(ret1[0].name)
print(ret1[0].id)
print(ret1[0].extra)

ret2 = session.query(Users.name, Users.extra).all()
print(ret2)

ret3 = session.query(Users).filter(Users.id < 3).all()  # 对象
print(ret3)

ret4 = session.query(Users).filter_by(name='zhoujie099').all()  # 对象
# ret4 = session.query(Users).filter_by(name='zhoujie099').first()  # 对象
print(ret4)

# 其他  之 条件
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1, 3, 4])).all()
ret = session.query(Users).filter(~Users.id.in_([1, 3, 4])).all()  # 非的意思
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

from sqlalchemy import and_, or_

ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()  # 并
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()  # 或的放肆
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()

# 其它 之 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()
# 其它 之 限制
ret = session.query(Users)[1:2]  # 区间
# 其它 之  排序   order_by
ret = session.query(Users).order_by(Users.name.desc()).all() #  desc是倒序
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()  # 按照第一个排序,如果第一个一样,再按照第二个

# 其它 之  分组  group_by
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) > 2).all()

# 其它 之  连表
ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()  # 将两个表进行连接 ,并且过滤 两个id相等的
ret = session.query(Person).join(Favor).all()  # 让两个表进行联合(默认是iner_join)
ret = session.query(Person).join(Favor, isouter=True).all()  # left join ,无right连表

# 其它 之  组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()   #联合,并且去重复数据

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()  #  联合,但是不会去掉重复数据
